package com.zd.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.zd.entity.User;


public class BaseDao {
	public static final String URL="jdbc:mysql://127.0.0.1:3306/endpoint?";
	public static final String USER="root";
	public static final String PASS="123456";
	public static final String DRIVER="com.mysql.cj.jdbc.Driver";
	
	protected Connection conn=null;
	protected PreparedStatement ps = null;

	/**
	 * 执行 添加 删除 修改的方法
	 * @param String SQL，Object占位符对应的值得数组 没有占位符传入null
	 * @RETURN int 影响行数 -1没有执行
	 * */
	public static int executeUpdate(String sql,Object[] param) {
		Connection conn=null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);//预编译sql
			if(param != null) {
				for (int i = 0; i < param.length; i++) {
					ps.setObject(i+1, param[i]);
				}
			}
			int num = ps.executeUpdate();//执行sql
			return num;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
				closeAll(conn, ps);
		}
		return -1;
	}
	
	/**
	 * 执行 添加 删除 修改的方法
	 * @param String SQL
	 * @RETURN int 影响行数
	 * */
	public static int executeUpdate(String sql) {
		Connection conn=null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);//预编译sql
			int num = ps.executeUpdate();
			return num;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
				closeAll(conn, ps);
		}
		return -1;
	}
	
	/**
	 * 获得数据库连接
	 * */
	public static Connection getConn() {
		try {
			Class.forName(DRIVER);
			Connection conn=DriverManager.getConnection(URL, USER, PASS);
			if(conn != null) {
				System.out.println("连接成功");
			}else {
				System.out.println("数据库连接失败");
			}
			return conn;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
		return null;
	}
	/**
	 * 释放资源Connection，PreparedStatement，ResultSet
	 * */
	public static void closeAll(Connection conn,Statement ps,ResultSet rs) {
		try {
			if(rs !=null) {rs.close();}
			if(ps !=null) {ps.close();}
			if(conn !=null) {conn.close();}
		} catch (SQLException e) {e.printStackTrace();}
	}
	
	/**
	 * 释放资源Connection，PreparedStatement
	 * */
	public static void closeAll(Connection conn,PreparedStatement ps) {
		try {
			if(ps !=null) {ps.close();}
			if(conn !=null) {conn.close();}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ResultSet queryResultSet(String sql) {
		conn=BaseDao.getConn();//打开连接
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	/**
	 * 查询案例
	 * 可借鉴
	 * */
	
	public static User query(String userId, String userPass) {
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "select * from user where userId='"+userId+"' and userPass='"+userPass+"' OR userPhone='"+userId+"' and userPass='"+userPass+"'";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			while(rs.next()) {
				User user = new User();
				user.setUserId(rs.getInt("userId"));
				user.setUserNickName(rs.getString("userNickName"));
				user.setUserPass(rs.getString("userPass"));
				user.setUserPhone(rs.getString("userPhone"));
				user.setExpValue(rs.getInt("ExpValue"));
				user.setStatus(rs.getInt("status"));
				user.setRisgterTime(rs.getDate("risgterTime"));
				user.setVip(rs.getInt("vip"));
				user.setAdmin(rs.getInt("admin"));
				return user;
			}
			return null;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	public static List<User> queryAll() {
		Connection conn=BaseDao.getConn();//打开连接
//		System.out.println(conn+"看看有啥");
		String sql = "SELECT * FROM user";
		List<User> list = new ArrayList<User>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while(rs.next()) {
				User user = new User();
				user.setUserId(rs.getInt("userId"));
				user.setUserNickName(rs.getString("userNickName"));
				user.setUserPass(rs.getString("userPass"));
				user.setUserPhone(rs.getString("userPhone"));
				user.setExpValue(rs.getInt("ExpValue"));
				user.setStatus(rs.getInt("status"));
				user.setRisgterTime(rs.getDate("risgterTime"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}
	
	public  ResultSet queryResultSet(String sql,Object[] params) {
		conn=BaseDao.getConn();//打开连接
		ResultSet rs=null;
		try {
			//预编译sql
			ps= conn.prepareStatement(sql);
			//更新占位符
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
			rs = ps.executeQuery();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	

//	public static int login(String userName,String userPass) {
//		Connection conn=BaseDao.getConn();
//		PreparedStatement s = null;
//		ResultSet rs = null;
//		try {
//			if(conn!=null) {
//				System.out.println("连接成功");
//			}
//			String sql = "select count(1) as num from user where username='"+userName+"' and userpass='"+userPass+"'" ;
//			System.out.println(sql);
//			s = conn.prepareStatement(sql);
//			rs =  s.executeQuery();
//			while(rs.next()) {
//				
//				return rs.getInt("num");
//			}
//			
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {			
//			BaseDao.closeAll(conn, s,rs);
//		}
//		return 0;
//	}
	
}
